# -*- coding: utf-8 -*-
import datetime

import os

import sqlite3

import tushare as ts

from app import app, db, lm

from app.models import Account, DailyAsset, DailyUpdate, DynamicHolding
from app.models import Holding, Strategy, TradingRecord, User

from math import floor

from flask import flash, redirect, render_template, request, url_for

from flask_login import login_required, login_user, logout_user

from flask_wtf import FlaskForm

from wtforms import BooleanField, FloatField, IntegerField
from wtforms import PasswordField, StringField, SubmitField

from wtforms.validators import DataRequired, EqualTo, ValidationError

from app.get_excel_files import read_excel_file

from app.get_k_data import get_tdx_stock_minutedata, sh_dir, sz_dir, tdx_dir


class LoginForm(FlaskForm):
    '''用户登陆表格'''
    username = StringField('用户名：', validators=[DataRequired()])
    password = PasswordField('密码：', validators=[DataRequired()])
    remember_me = BooleanField('记住我')
    submit = SubmitField('登入')


class RegisterForm(FlaskForm):
    '''用户注册表格'''
    username = StringField('用户名：', validators=[DataRequired()])
    password = PasswordField('新密码：', validators=[DataRequired(),
                             EqualTo('password2',
                                     message='两次密码输入必须一致')])
    password2 = PasswordField('重复密码', validators=[DataRequired()])
    submit = SubmitField('注册')

    def validate_username(self, field):
        if User.query.filter_by(username=field.data).first():
            raise ValidationError('用户名已存在')


class StrategyForm(FlaskForm):
    name = StringField('策略名称：', validators=[DataRequired()])
    title = StringField('策略标题名：', validators=[DataRequired()])
    fee = FloatField('交易费率：', validators=[DataRequired()])
    init_cash = IntegerField('初始资金：', validators=[DataRequired()])
    submit = SubmitField('提交')


@lm.user_loader
def load_user(id):
    return User.query.get(int(id))

# @app.before_request
# def before_request():
    # g.user = current_user


@app.route('/login', methods=['POST', 'GET'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is not None and user.verify_password(form.password.data):
            login_user(user, form.remember_me.data)
            return redirect(url_for('index'))
            flash('登录成功！', 'success')
            return render_template('index.html')
        else:
            flash('用户名或密码错误，请重新输入。', 'danger')
    return render_template('login.html', form=form)


@app.route('/logout')
def logout():
    logout_user()
    return redirect(url_for('login'))


@app.route('/', methods=['GET', 'POST'])
# @login_required
def index():
    strategy_form = StrategyForm()
    if request.method == 'POST':
        if strategy_form.validate():
            name = strategy_form.name.data
            title = strategy_form.title.data
            fee = strategy_form.fee.data
            init_cash = strategy_form.init_cash.data
            strategy = Strategy(name, title, fee, init_cash)
            db.session.add(strategy)
            db.session.commit()
    else:
        print(request.method)
    strategy = db.session.query(Strategy).all()
    print(strategy)
    print(len(strategy))
    return render_template('index.html',
                           form=strategy_form,
                           strategy_list=strategy,
                           strategy_count=len(strategy))


@app.route('/strategy/<strategy_id>')
@login_required
def strategy(strategy_id):
    strategy = Strategy.query.filter_by(id=strategy_id).first()
    print(strategy)
    strategy_name = strategy.title
    trading_records = strategy.trading_record.all()
    holdings = strategy.holding.all()
    daily_assets = strategy.daily_asset.all()
    daily_updates = strategy.daily_update.all()
    #TODO 继续补充，查询该策略的交易记录及资产情况
    return render_template('strategy.html',
                           strategy_name=strategy_name,
                           trading_records=trading_records,
                           holdings=holdings,
                           daily_assets=daily_assets,
                           daily_updates=daily_updates)


@app.route('/strategy/<strategy_id>/<year>/<month>/<day>')
@login_required
def strategy_detail(strategy_id, year, month, day):
    date_str = format_date(year, month, day)
    trading_records = TradingRecord.query.filter_by(
        sid=strategy_id,
        date=date_str).all()
    holdings = Holding.query.filter_by(
        sid=strategy_id,
        date=date_str).all()
    daily_updates = DailyUpdate.query.filter_by(
        sid=strategy_id,
        date=date_str).all()
    daily_asset = DailyAsset.query.filter_by(
        sid=strategy_id,
        date=date_str).first()
    return render_template('detail.html',
                           trading_records=trading_records,
                           holdings=holdings,
                           daily_asset=daily_asset,
                           daily_updates=daily_updates)


@app.route('/register', methods=['POST', 'GET'])
def register():
    form = RegisterForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is not None:
            flash('用户已存在，请重新选择用户名。', 'danger')
            return render_template('register.html', form=form)
        else:
            new_user = User(form.username.data, form.password.data)
            new_user.authenticated = True
            db.session.add(new_user)
            db.session.commit()
            login_user(new_user)
            return redirect(url_for('index'))
    return render_template('register.html', form=form)


@app.route('/run')
#手动运行一次计算
def run():
    #获取策略名称及ID
    strategys = db.session.query(Strategy).all()
    strategy_dict = {}
    for strategy in strategys:
        strategy_dict[strategy.title] = strategy.id
    #开始读取输入表格
    log = []
    all_strategy_list = read_excel_file()
    log.append('找到需要处理的 %s 个输入表格。' % len(all_strategy_list))
    for strategy_list in all_strategy_list:
        log.append('当前表格有 %s 列' % (len(strategy_list)))
        for i in range(len(strategy_list)):
            strategy = strategy_list[i]
            if i == 0 and isinstance(strategy[0], str):
                index = strategy[0].find('午盘逢低买入T+1')
                print(strategy[0], index)
                if index > 0:
                    dt_str = strategy[0][0:index]
                    date = format_date(dt_str)
                else:
                    date = format_date(strategy[0])
                log.append('当前策略的日期为：%s' % date)
            strategy_id = -1
            for j in range(len(strategy)):
                if j == 0 and strategy[j]:
                    for key in strategy_dict:
                        print('key=%s, strategy[0]=%s, i=%s, j=%s' % (
                            key, strategy[0], i, j))
                        log.append('key=%s, strategy[0]=%s' % (
                            key, strategy[0]))
                        if key in str(strategy[0]):
                            strategy_id = strategy_dict[key]
                            log.append('找到 %s 号策略：%s，开始处理。' % (
                                       strategy_id, key))
                            break
                #找到对应策略
                else:
                    if strategy_id > 0:
                        strategy = Strategy.query.filter_by(
                            id=strategy_id).first()
                        # log.append(('找到策略ID %s，以及内容：%s'
                        #             '' % (strategy_id, strategy)))
                        stockid, market = strategy[j].split('.')
                        print('stockid=%s, market=%s' % (stockid, market))
                        daily_update = DailyUpdate.query.filter(
                            DailyUpdate.strategy == strategy,
                            DailyUpdate.date == date,
                            DailyUpdate.stockid == stockid).first()
                        if not daily_update:
                            daily_update = DailyUpdate(date,
                                                       stockid,
                                                       strategy)
                            db.session.add(daily_update)
                            log.append('新增加 %s 策略内容: %s' % (strategy.title,
                                       daily_update))
                        else:
                            daily_update.date = format(date)
                            daily_update.stock = stockid
                            log.append('记录已经存在！更新策略内容：%s' % daily_update)
                        db.session.commit()
    return render_template('run_result.html', log=log)


@app.route('/update')
#手动运行一次计算
def update():
    log = []
    strategies = Strategy.query.order_by(Strategy.id).all()
    mkt_data_dict = {}
    # log = []
    #预设早上交易时间
    preset_morning_time = "10:00"
    #预设下午交易时间
    preset_afternoon_time = "14:00"
    for strategy in strategies:
        #清除动态持仓表
        dynamic_holding = DynamicHolding.query.all()
        for holding in dynamic_holding:
            db.session.delete(holding)
            db.session.commit()
            print('策略开始，清除动态持仓表 DynamicHolding。')
            log.append('策略开始，清除动态持仓表 DynamicHolding。')
        dates = []
        date_list = db.session.query(DailyUpdate.date).filter_by(
            strategy=strategy).group_by(
            DailyUpdate.date).all()
        fee_rate = strategy.fee_rate
        cash = strategy.init_cash
        print('init_cash=%s' % cash)
        log.append('init_cash=%s' % cash)
        if strategy.id in [1, 3]:
            preset_time = preset_morning_time
        else:
            preset_time = preset_afternoon_time
        print('preset_time=%s' % preset_time)
        for obj in date_list:
            dt = datetime.datetime.strptime(obj[0], "%Y-%m-%d")
            dates.append(dt)
        dates.sort()
        # print(dates)
        for dt in dates:
            date = dt.strftime("%Y-%m-%d")
            #每天根据买入股票，按收盘价累计
            holding_asset = 0
            #卖出原有持仓
            dynamic_holding_list = DynamicHolding.query.all()
            for holding in dynamic_holding_list:
                #匹配行情记录，计算卖出价格
                holding_date, holding_time = holding.dt.split(' ')
                print('holding_date=%s, holding_time=%s' % (holding_date,
                                                            holding_time))
                stockid = holding.stockid
                log.append('len(mkt_data_dict)=%s' % len(mkt_data_dict))
                if stockid in mkt_data_dict:
                    history = mkt_data_dict[stockid]
                    print('缓存中没有 %s 的行情，现已加入。' % stockid)
                    log.append('缓存中没有 %s 的行情，现已加入。' % stockid)
                else:
                    tdx_filename = get_tdx_filename(stockid)
                    history = get_tdx_stock_minutedata(tdx_filename, stockid)
                    mkt_data_dict[stockid] = history
                    print('缓存中存在 %s 的行情，无需再次读取。' % stockid)
                    log.append('缓存中存在 %s 的行情，无需再次读取。' % stockid)
                print('len(history)=%s' % len(history))
                i = 0
                found_date = False
                selling_asset = 0
                while i < len(history) - 1:
                    record = history[i]
                    record2 = history[i + 1]
                    # log.append('record:%s' % record)
                    # log.append('record2:%s' % record2)
                    history_date, history_time = record[1].split(' ')
                    history_date2, history_time2 = record2[1].split(' ')
                    #找到对应操作日期和时间
                    if (holding_date == history_date and
                       holding_date != history_date2):
                        print('找到持仓 %s 对应的卖出日期：%s' % (
                            holding, history_date2))
                        log.append('找到持仓 %s 对应的卖出日期：%s' % (
                            holding, history_date2))
                        found_date = True
                    if found_date:
                        selling_asset = -1
                        # 找到买入时间的前一根K线，9:55和13:55
                        if history_time2 == preset_time:
                            print('找到持仓 %s 对应的卖出时间：%s %s' % (
                                holding, history_date, history_time))
                            log.append('找到持仓 %s 对应的卖出时间：%s %s' % (
                                holding, history_date, history_time))
                            price = round(record[2], 2)
                            amount = holding.amount
                            #删除对应持仓
                            db.session.delete(holding)
                            db.session.commit()
                            #TODO 此处需要补充插入交易记录
                            trans = round(price * amount * 100, 2)
                            fee = round(trans * fee_rate, 2)
                            trading_record = TradingRecord.query.filter(
                                TradingRecord.stockid == stockid).filter(
                                TradingRecord.date == record[1]).filter(
                                TradingRecord.price == price).filter(
                                TradingRecord.amount == amount).first()
                            if trading_record:
                                trading_record.stockid = stockid
                                trading_record.date = record[1]
                                trading_record.price = price
                                trading_record.amount = 0 - amount
                                trading_record.trans = round(
                                    price * amount * 100, 2)
                                trading_record.fee = fee
                                print('交易记录表的记录已存在，现已更新：%s' % trading_record)
                                log.append('交易记录表的记录已存在，现已更新：%s' % trading_record)
                            else:
                                trading_record = TradingRecord(
                                    record[1], stockid, price, 0 - amount,
                                    trans, fee, strategy)
                                db.session.add(trading_record)
                                print('增加交易记录表的记录：%s' % trading_record)
                                log.append('增加交易记录表的记录：%s' % trading_record)
                            db.session.commit()
                            #更新资产变化
                            selling_asset = round(price * amount * 100, 2)
                            old_cash = cash
                            cash = round(cash + selling_asset - fee, 2)
                            print(('卖出后获得 %s 的现金，支出 %s 手续费，现金从 %s '
                                  '变为 %s。' % (selling_asset, fee, old_cash, cash)))
                            log.append(('卖出后获得 %s 的现金，支出 %s 手续费，现金从 %s '
                                        '变为 %s。' % (selling_asset, fee, old_cash, cash)))
                            break
                    i = i + 1
                if i == len(history) - 1:
                    print('没有找到持仓 %s 对应的历史行情记录：%s %s' % (
                        holding, history_date, history_time))
                    log.append('没有找到持仓 %s 对应的历史行情记录：%s %s' % (
                        holding, history_date, history_time))
            #买入新的持仓
            record_count = DailyUpdate.query.filter_by(date=date).count()
            print('策略 %s 在日期 %s 共有 %s 条记录。' % (strategy, date, record_count))
            cash_unit = floor(cash / record_count)
            print('总现金为%s, 需要买入%s个股票，每份买入金额为%s' % (
                cash, record_count, cash_unit))
            log.append('总现金为%s, 需要买入%s个股票，每份买入金额为%s' % (
                cash, record_count, cash_unit))
            daily_updates = DailyUpdate.query.filter_by(
                strategy=strategy, date=date).order_by(DailyUpdate.date).all()
            for daily_update in daily_updates:
                log.append('根据策略内容买入：%s' % daily_update)
                print('根据策略内容买入：%s' % daily_update)
                date_str = daily_update.date
                stockid = daily_update.stockid
                log.append('len(mkt_data_dict)=%s' % len(mkt_data_dict))
                if stockid in mkt_data_dict:
                    history = mkt_data_dict[stockid]
                    print('缓存中没有 %s 的行情，现已加入。' % stockid)
                    log.append('缓存中没有 %s 的行情，现已加入。' % stockid)
                else:
                    tdx_filename = get_tdx_filename(stockid)
                    history = get_tdx_stock_minutedata(tdx_filename, stockid)
                    mkt_data_dict[stockid] = history
                    print('缓存中存在 %s 的行情，无需再次读取。' % stockid)
                    log.append('缓存中存在 %s 的行情，无需再次读取。' % stockid)
                    log.append('len(mkt_data_dict)=%r' % len(mkt_data_dict))
                # print('len(history)= %s' % len(history))
                # log.append('history: %s' % history)
                i = 0
                is_buying = False
                price = -1
                amount = -1
                print('len(history)=%s ' % len(history))
                log.append('len(history)=%s ' % len(history))
                while i < len(history):
                    record = history[i]
                    # print('len(record[%s]): %s' % (i, len(record)))
                    history_date, history_time = record[1].split(' ')
                    # log.append(('history_date=%s, history_time=%s, dt_str=%s'
                    #         '' % (history_date, history_time, dt_str)))
                    #找到对应操作日期和时间
                    if (history_date == date_str and
                       history_time == preset_time):
                        is_buying = True
                        price = round(record[2], 2)
                        amount = round(cash_unit / price / 100)
                        name = ts.get_stock_basics().ix[stockid][0]
                        dt_str = ('%s %s' % (date_str, preset_time))
                        holding = DynamicHolding.query.filter_by(
                            dt=date, stockid=stockid, strategy=strategy).first()
                        if holding:
                            holding.price = price
                            holding.amount = amount
                            print('动态持仓记录已存在？？已更新：%s' % holding)
                            log.append('动态持仓记录已存在？？已更新：%s' % holding)
                        else:
                            holding = DynamicHolding(dt_str, name, stockid,
                                                     price, amount, strategy)
                            db.session.add(holding)
                            print('增加动态持仓记录：%s' % holding)
                            log.append('增加动态持仓记录：%s' % holding)
                        db.session.commit()
                        #增加交易记录
                        trans = round(price * amount * 100, 2)
                        fee = round(trans * fee_rate, 2)
                        trading_record = TradingRecord.query.filter(
                            TradingRecord.stockid == stockid,
                            TradingRecord.date == record[1],
                            TradingRecord.price == price,
                            TradingRecord.amount == amount).first()
                        if trading_record:
                            trading_record.stockid = stockid
                            trading_record.date = record[1]
                            trading_record.price = price
                            trading_record.amount = amount
                            trading_record.trans = round(
                                price * amount * 100, 2)
                            trading_record.fee = round(
                                trading_record.trans * fee_rate, 2)
                            print('交易记录表的记录已存在，现已更新：%s' % trading_record)
                            log.append('交易记录表的记录已存在，现已更新：%s' % trading_record)
                        else:
                            trading_record = TradingRecord(
                                record[1], stockid, price, amount,
                                trans, fee, strategy)
                            db.session.add(trading_record)
                            print('增加交易记录表的记录：%s' % trading_record)
                            log.append('增加交易记录表的记录：%s' % trading_record)
                            db.session.commit()
                        buying_asset = round(price * amount * 100, 2)
                        old_cash = cash
                        cash = round(cash - buying_asset - fee, 2)
                        print(('买入后支出 %s 的现金，支出 %s 手续费，现金从 %s '
                              '变为 %s。' % (buying_asset, fee, old_cash, cash)))
                        log.append(('买入后支出 %s 的现金，支出 %s 手续费，现金从 %s '
                                 '变为 %s。' % (buying_asset, fee, old_cash,
                                  cash)))
                    #增加收盘持仓记录
                    elif (history_date == date_str and is_buying and
                          history_time == "15:00"):
                        price = round(record[2], 2)
                        name = ts.get_stock_basics().ix[stockid][0]
                        fee = round(price * amount * 100 * fee_rate)
                        buying_asset = round(price * amount * 100, 2)
                        holding_asset = holding_asset + buying_asset
                        holding = Holding.query.filter_by(
                            dt=date_str,
                            stockid=stockid,
                            strategy=strategy).first()
                        if holding:
                            holding.price = price
                            holding.amount = amount
                            print('收盘持仓表的记录已存在，现已更新：%s' % holding)
                            log.append('收盘持仓表的记录已存在，现已更新：%s' % holding)
                        else:
                            holding = Holding(date_str, name, stockid,
                                              price, amount, strategy)
                            db.session.add(holding)
                            print('收盘持仓表的记录不存在，新建记录：%s' % holding)
                            log.append('收盘持仓表的记录不存在，新建记录：%s' % holding)
                        db.session.commit()
                        break
                    i += 1
                if i == len(history):
                    print(('没有找到匹配行情 history_date=%s, history_time=%s, '
                           'dt_str=%s' % (history_date, history_time, dt_str)))
                    log.append(('没有找到匹配行情 history_date=%s, history_time=%s, '
                              'dt_str=%s' % (history_date, history_time,
                                             dt_str)))
            #收盘记录持仓，以及当天资产状况
            asset = round(cash + holding_asset, 2)
            daily_asset = DailyAsset.query.filter_by(
                date=date, strategy=strategy).first()
            if daily_asset:
                daily_asset.cash = round(cash, 2)
                daily_asset.holding = round(holding_asset, 2)
                daily_asset.asset = round(cash + holding_asset, 2)
                print('收盘资产记录已存在，已经进行更新：%s' % daily_asset)
                log.append('收盘资产记录已存在，已经进行更新：%s' % daily_asset)
            else:
                daily_asset = DailyAsset(date, cash, holding_asset, asset, strategy)
                db.session.add(daily_asset)
                print('cash=%s, holding_asset=%s, asset=%s' % (cash, holding_asset, asset))
                print('收盘资产记录不存在，新增：%r' % daily_asset)
                log.append('收盘资产记录不存在，新增：%r' % daily_asset)
            db.session.commit()
    return render_template('run_result.html', log=log)


def format_date(dt_str):
    #统一输出为YYYY-mm-dd
    _list = dt_str.split('.')
    #类似20180111
    if len(_list) <= 1 and len(dt_str) == 8:
        year = dt_str[0:4]
        month = dt_str[4:6]
        day = dt_str[6:8]
    #类似2018.1.11
    else:
        year, month, day = dt_str.split('.')
    if len(month) == 1:
        month = '0' + month
    if len(day) == 1:
        day = '0' + day
    #统一输出为2018-01-11
    date = '%s-%s-%s' % (year, month, day)
    return date


def format_date(year, month, day):
    #输入为4位数字年份，以及月、日的数字
    #统一输出为YYYY-mm-dd
    if month < 10:
        month_str = '0' + str(month)
    else:
        month_str = str(month)
    if day < 10:
        day_str = '0' + str(day)
    else:
        day_str = str(day)
    date = '%s-%s-%s' % (str(year), month_str, day_str)
    return date


def get_tdx_filename(stockid):
    if stockid[0] == '6':
        subdir = sh_dir
        mkt = 'sh'
    else:
        subdir = sz_dir
        mkt = 'sz'
    filename = '%s%s.lc5' % (mkt, stockid)
    tdx_filename = os.path.join(tdx_dir, subdir, filename)
    return tdx_filename
